import time

# import MySQLdb
import traceback

import pymysql

# pymysql.install_as_MySQLdb()


class MyDB(object):
    def __init__(self, host, port, user, pwd, dbname, charset="utf8"):
        self._db_conn = None
        self._db_host = host
        self._db_port = port
        self._db_user = user
        self._db_pwd = pwd
        self._db_name = dbname
        self._db_charset = charset

        self.connect()

    def __del__(self):
        self.close()

    def close(self):
        try:
            if self._db_conn:
                self._db_conn.close()
                self._db_conn = None
        except Exception as e:
            print("in close(): {}".format(e))

    def ping(self):
        try:
            self._db_conn.ping()
            return True
        except Exception as e:
            print("in ping(): {}".format(e))
            return False

    def connect(self):
        try:
            self._db_conn = pymysql.connect(
                host=self._db_host,
                user=self._db_user,
                passwd=self._db_pwd,
                db=self._db_name,
                port=self._db_port,
                charset=self._db_charset,
            )
            self._db_conn.autocommit(False)
        except:
            print(traceback.format_exc())

    def reconnect(self):
        try:
            while True:
                if not self.ping():
                    self.connect()
                else:
                    break
                time.sleep(1)
        except Exception as e:
            print(traceback.format_exc())
            print("in reconnect(): {}".format(e))

    def commit(self):
        try:
            self._db_conn.commit()
        except Exception as e:
            self.rollback()
            print("in commit(): {}".format(e))
            print(traceback.format_exc())

    def rollback(self):
        try:
            self._db_conn.rollback()
        except Exception as e:
            print("in rollback(): {}".format(e))
            print(traceback.format_exc())

    def execute(self, sql, para=None):
        try:
            self.reconnect()
            cursor = self._db_conn.cursor()
            cursor.execute(sql, para)
            self.commit()
            cursor.close()
            return True
        except Exception as e:
            cursor.close()
            print("in execute(): {}".format(e))
            print(traceback.format_exc())
            return False

    def executemany(self, sql, para=None):
        try:
            self.reconnect()
            cursor = self._db_conn.cursor()
            cursor.executemany(sql, para)
            self.commit()
            cursor.close()
            return cursor.rowcount
        except Exception as e:
            cursor.close()
            print("in executemany(): {}".format(e))
            print(traceback.format_exc())
            return None

    def query(self, sql, para=None, as_dic=False):
        try:
            self.reconnect()
            if as_dic:
                cursor = self._db_conn.cursor(pymysql.cursors.DictCursor)
            else:
                cursor = self._db_conn.cursor()
            cursor.execute(sql, para)
            result = cursor.fetchall()
            self.commit()
            cursor.close()
            return result
        except Exception as e:
            cursor.close()
            print("in query(): {}".format(e))
            print(traceback.format_exc())
            return None


def run_test():
    testdb = MyDB(
        "192.168.83.70", 13306, "root", "Zx123456@shining11", "hb_db"
    )

    table = """
            CREATE TABLE IF NOT EXISTS `watchdog`(
                `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `name` varchar(100),
                `price` int(11) NOT NULL DEFAULT 0
            ) ENGINE=InnoDB charset=utf8;
            """
    print(testdb.execute(table))

    # delete
    sql = "delete from watchdog;"
    print(testdb.execute(sql))

    # execute one
    sql = 'INSERT INTO `watchdog`(`name`,`price`) VALUES("%s",%d);' % (
        "dog_test1",
        12,
    )
    print(testdb.execute(sql))

    params = ("dog_test2", 24)
    sql = "INSERT INTO `watchdog`(`name`,`price`) VALUES(%s,%s);"
    print(testdb.execute(sql, params))
    # insert into tbl (x1,x2,x3) values ()

    # execute many
    params = [("dog_%d" % i, i) for i in range(12)]
    print(params)
    sql = "INSERT INTO `watchdog`(`name`,`price`) VALUES(%s,%s);"
    print(testdb.executemany(sql, params))

    # insert into tbl (x1,x2,x3) values (),(),()

    # query
    sql = "select * from watchdog"
    print(testdb.query(sql))

    # query with condition
    sql = "select * from watchdog where price=%d" % (10)
    print(testdb.query(sql))
    print(testdb.query(sql, as_dic=True))
    sql = "select avg(price) from watchdog where price=%s"
    print(testdb.query(sql, (10,)))
    print(testdb.query(sql, (10,), as_dic=True))

    # drop test table
    sql = "drop table watchdog;"
    print(testdb.execute(sql))


if __name__ == "__main__":
    run_test()
